package dao;

import pojo.Book;
import pojo.Pager;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BookSql {
    //获取特惠书籍前四行
    public static  List<Book> querySpecial() throws SQLException {
        List<Book> list=new ArrayList<>();
        String sql="SELECT * FROM book where special=1 limit 0,4";
        ResultSet rs = JDBCconection.getData(sql);
        Book book=null;
        while (rs.next()){
            book=new Book();
            book.setId(rs.getInt(1));
            book.setName(rs.getString(2));
            book.setAuthor(rs.getString(3));
            book.setDescript(rs.getString(4));
            book.setCategory(rs.getString(5));
            book.setPrice(rs.getDouble(6));
            book.setDatetime(rs.getString(7));
            book.setImg(rs.getString(8));
            book.setSpecial(rs.getInt(9));
            book.setHot(rs.getInt(10));
            list.add(book);
        }
        return list;
    }

    public static  List<Book> querySpecialAll() throws SQLException {
        List<Book> list=new ArrayList<>();
        String sql="SELECT * FROM book where special=1";
        ResultSet rs = JDBCconection.getData(sql);
        Book book=null;
        while (rs.next()){
            book=new Book();
            book.setId(rs.getInt(1));
            book.setName(rs.getString(2));
            book.setAuthor(rs.getString(3));
            book.setDescript(rs.getString(4));
            book.setCategory(rs.getString(5));
            book.setPrice(rs.getDouble(6));
            book.setDatetime(rs.getString(7));
            book.setImg(rs.getString(8));
            book.setSpecial(rs.getInt(9));
            book.setHot(rs.getInt(10));
            list.add(book);
        }
        return list;
    }

    //获取热门书籍的前八行
    public static List<Book> queryHot() throws SQLException {
        List<Book> list=new ArrayList<>();
        String sql="SELECT * FROM book where hot=1 limit 0,8";
        ResultSet rs = JDBCconection.getData(sql);
        Book book=null;
        while (rs.next()){
            book=new Book();
            book.setId(rs.getInt(1));
            book.setName(rs.getString(2));
            book.setAuthor(rs.getString(3));
            book.setDescript(rs.getString(4));
            book.setCategory(rs.getString(5));
            book.setPrice(rs.getDouble(6));
            book.setDatetime(rs.getString(7));
            book.setImg(rs.getString(8));
            book.setSpecial(rs.getInt(9));
            book.setHot(rs.getInt(10));
            list.add(book);
        }
        return list;
    }

    public static List<Book> queryHotAll() throws SQLException {
        List<Book> list=new ArrayList<>();
        String sql="SELECT * FROM book where hot=1";
        ResultSet rs = JDBCconection.getData(sql);
        Book book=null;
        while (rs.next()){
            book=new Book();
            book.setId(rs.getInt(1));
            book.setName(rs.getString(2));
            book.setAuthor(rs.getString(3));
            book.setDescript(rs.getString(4));
            book.setCategory(rs.getString(5));
            book.setPrice(rs.getDouble(6));
            book.setDatetime(rs.getString(7));
            book.setImg(rs.getString(8));
            book.setSpecial(rs.getInt(9));
            book.setHot(rs.getInt(10));
            list.add(book);
        }
        return list;
    }

    public static void pageQuery(Pager pager ,String category,String bname){
        findByPage(pager,category, bname);
    }
    //分页查询图书
    public static void findByPage(Pager pager,String category,String bname){
        List<Book> book=new ArrayList<>();
        //获取图书数据列表
        //获取从第几条数据开始
        int currentPage=(pager.getCurrentPage()-1)*pager.getPageSize();
        //String
        String sql1="select * from book  where category like '%"+category+"%' and name like '%"+bname+"%' limit "+currentPage+" ,"+pager.getPageSize()+"";
        ResultSet rs = JDBCconection.getData(sql1);
        try{
            Book b=null;
            while (rs.next()){
                b=new Book();
                b.setId(rs.getInt(1));
                b.setName(rs.getString(2));
                b.setAuthor(rs.getString(3));
                b.setDescript(rs.getString(4));
                b.setCategory(rs.getString(5));
                b.setPrice(rs.getDouble(6));
                b.setDatetime(rs.getString(7));
                b.setImg(rs.getString(8));
                b.setSpecial(rs.getInt(9));
                b.setHot(rs.getInt(10));
                book.add(b);
            }
            if(category==""&&bname==""){
                //查询总记录
                sql1="select count(*) from book";
                rs = JDBCconection.getData(sql1);
                if (rs.next()){
                    pager.setTotal(rs.getInt(1));
                }
            }
            else if(category!=""||bname!=""){
                sql1="select count(*) from book where category like '%"+category+"%' and name like '%"+bname+"%'";
                rs = JDBCconection.getData(sql1);
                if (rs.next()){
                    pager.setTotal(rs.getInt(1));
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        pager.setRs(book);
    }
    //删除图书信息
    public static void deleteBook(Integer id){
        String sql="delete from book where id="+id+"";
        JDBCconection.updata(sql);
    }

    //修改书籍信息
    public static Boolean updateBook(Book book){
        String sql="update book set name='"+book.getName()+"',author='"+book.getAuthor()+"',descript='"+book.getDescript()+"',category='"+book.getCategory()+"',price="+book.getPrice()+" " +
                ",datetime='"+book.getDatetime()+"',img='"+book.getImg()+"',special="+book.getSpecial()+",hot="+book.getHot()+" where id="+book.getId()+"";
        if(JDBCconection.updata(sql)==true){
            return true;
        }else {
            return false;
        }
    }
    //添加书籍信息
    public static Boolean addBook(Book book){
        Boolean flag=false;
        String sql="insert into book value(null,'"+book.getName()+"','"+book.getAuthor()+"','"+book.getDescript()+"','"+book.getCategory()+"'" +
                ","+book.getPrice()+",'"+book.getDatetime()+"','"+book.getImg()+"',"+book.getSpecial()+","+book.getHot()+")";
        if (JDBCconection.updata(sql)==true){
            flag=true;
            return flag;
        }else {
            return flag;
        }
    }

}
